/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aptech.DAO;

import com.aptech.model.realEstateInfo;
import com.aptech.model.RealEstate;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Hoang Quy Hong An
 */
public class realEstateDAO {

    private Connection myCon = null;
    private PreparedStatement ps = null;
    private ResultSet rs = null;
    private String strCommand = "";

    private boolean checkDate(String d1) {
        boolean flag = false;
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
            Calendar calendar = Calendar.getInstance();
            int day = calendar.get(Calendar.DAY_OF_MONTH);
            int month = calendar.get(Calendar.MONTH);
            int year = calendar.get(Calendar.YEAR);
            //        System.out.println("Start date : " + day + "/" + (month + 1) + "/" + year);
            //        System.out.println("End date : " + day + "/" + (month + 2) + "/" +year);
            Date date1 = new Date(sdf.parse(d1).getTime());
            String d2 = day + "/" + (month + 1) + "/" + year;
            Date date2 = new Date(sdf.parse(d2).getTime());
            if (date1.getTime() > date2.getTime()) {
                flag = true;
            }
        } catch (ParseException ex) {
            Logger.getLogger(realEstateDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return flag;
    }

    public ArrayList<realEstateInfo> getAllRealEstate() {
        ArrayList<realEstateInfo> listrealEstateInfo = new ArrayList<realEstateInfo>();

        int realEstateID;
        String realEstateTitle;
        String description;
        String realEstateImage;
        String startDate;
        String endDate;
        int userID;
        int postHistoryID;
        realEstateInfo rei = null;
        postHistoryDAO objPostHisDAO;
        strCommand = "SELECT tbl_RealEstate.RealEstateID, tbl_RealEstate.realEstateTitle, tbl_RealEstate.Description,"
                + " tbl_RealEstate.realEstateImage, tbl_PostHistory.postHistoryID, tbl_PostHistory.startDate, "
                + "tbl_PostHistory.endDate, tbl_PostHistory.userID "
                + "FROM tbl_PostHistory INNER JOIN "
                + "tbl_RealEstate ON tbl_PostHistory.RealEstateID = tbl_RealEstate.RealEstateID WHERE tbl_PostHistory.postHistoryStatus = 1 AND tbl_RealEstate.realEstateStatus =1";

        try {
            objPostHisDAO = new postHistoryDAO();
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rs = ps.executeQuery();
            while (rs.next()) {
                boolean flag = false;
                rei = new realEstateInfo();
                realEstateID = rs.getInt("RealEstateID");
                realEstateTitle = rs.getString("realEstateTitle");
                description = rs.getString("Description");
                realEstateImage = rs.getString("realEstateImage");
                startDate = rs.getString("startDate");
                endDate = rs.getString("endDate");
                userID = rs.getInt("userID");
                postHistoryID = rs.getInt("postHistoryID");
                flag = checkDate(endDate);
                if (flag == true) {
                    rei.setRealEstateID(realEstateID);
                    rei.setRealEstateTitle(realEstateTitle);
                    rei.setDescription(description);
                    rei.setRealEstateImage(realEstateImage);
                    rei.setStartDate(startDate);
                    rei.setEndDate(endDate);
                    rei.setUserID(userID);
                    listrealEstateInfo.add(rei);
                } else {
                    objPostHisDAO.closePost(postHistoryID);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, rs);
        }
        return listrealEstateInfo;
    }

     public ArrayList<realEstateInfo> searchRealEstate(String strSearchCommand) {
        ArrayList<realEstateInfo> listrealEstateInfo = new ArrayList<realEstateInfo>();
         System.out.println("Den day search roi");
        int realEstateID;
        String realEstateTitle;
        String description;
        String realEstateImage;
        String startDate;
        String endDate;
        int userID;
        int postHistoryID;
        realEstateInfo rei = null;
        postHistoryDAO objPostHistoryDAO;
        strCommand = "SELECT tbl_RealEstate.RealEstateID, tbl_RealEstate.realEstateTitle, tbl_RealEstate.Description,"
                + " tbl_RealEstate.realEstateImage, tbl_PostHistory.postHistoryID, tbl_PostHistory.startDate, "
                + "tbl_PostHistory.endDate, tbl_PostHistory.userID "
                + "FROM tbl_PostHistory INNER JOIN "
                + "tbl_RealEstate ON tbl_PostHistory.RealEstateID = tbl_RealEstate.RealEstateID WHERE tbl_PostHistory.postHistoryStatus = 1 AND tbl_RealEstate.realEstateStatus =1 "+strSearchCommand;
                System.out.println("Commad: "+strCommand);
        try {
            objPostHistoryDAO = new postHistoryDAO();
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rs = ps.executeQuery();
            while (rs.next()) {
                boolean flag = false;
                rei = new realEstateInfo();
                realEstateID = rs.getInt("RealEstateID");
                realEstateTitle = rs.getString("realEstateTitle");
                description = rs.getString("Description");
                realEstateImage = rs.getString("realEstateImage");
                startDate = rs.getString("startDate");
                endDate = rs.getString("endDate");
                userID = rs.getInt("userID");
                postHistoryID = rs.getInt("postHistoryID");
                flag = checkDate(endDate);
                if (flag == true) {
                    rei.setRealEstateID(realEstateID);
                    rei.setRealEstateTitle(realEstateTitle);
                    rei.setDescription(description);
                    rei.setRealEstateImage(realEstateImage);
                    rei.setStartDate(startDate);
                    rei.setEndDate(endDate);
                    rei.setUserID(userID);
                    listrealEstateInfo.add(rei);
                } else {
                    objPostHistoryDAO.closePost(postHistoryID);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, rs);
        }
        return listrealEstateInfo;
    }

    public ArrayList<RealEstate> getRealEstateByID(int RealEstateID) {
        ArrayList<RealEstate> listRealEstate = new ArrayList<RealEstate>();
        RealEstate re = null;
        strCommand = "SELECT * FROM tbl_RealEstate WHERE RealEstateID = ? AND realEstateStatus = 1";
        try {

            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setInt(1, RealEstateID);
            rs = ps.executeQuery();
            while (rs.next()) {
                re = new RealEstate();
                re.setRealEstateID(rs.getInt("RealEstateID"));
                re.setCity(rs.getString("City"));
                re.setStreet(rs.getString("Street"));
                re.setPrice(rs.getString("Price"));
                re.setNoOfBedrooms(rs.getInt("NoOfBedrooms"));
                re.setNoOfBathrooms(rs.getInt("NoOfBathrooms"));
                re.setRealEstateTypeID(rs.getInt("realEstateTypeID"));
                re.setArea(rs.getString("area"));
                re.setModesOfTransport(rs.getString("ModesOfTransport"));
                re.setModeOfPayment(rs.getString("ModeOfPayment"));
                re.setDeposit(rs.getString("Deposit"));
                re.setNegotiable(rs.getInt("Negotiable"));
                re.setDescription(rs.getString("Description"));
                re.setImage(rs.getString("realEstateImage"));
                re.setRealEstateStyleID(rs.getInt("realEstateStyleID"));
                re.setRealEstateStatus(rs.getInt("realEstateStatus"));
                listRealEstate.add(re);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, rs);
        }
        return listRealEstate;
    }

    public boolean deleteRealEstate(int RealEstateID) {
        boolean flag = false;
        strCommand = "UPDATE tbl_RealEstate SET realEstateStatus = 0 WHERE RealEstateID = ?";
        try {
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setInt(1, RealEstateID);
            int i = ps.executeUpdate();
            if (i != 0) {
                flag = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, null);
        }
        return flag;
    }

    public int insertRealEstate(RealEstate objRe) {

        strCommand = "INSERT INTO tbl_RealEstate (realEstateTitle ,City ,Street ,Price "
                + ",NoOfBedrooms ,NoOfBathrooms ,realEstateTypeID ,area ,orientation, ExtraFacilitiesAvailable "
                + ",ModesOfTransport ,WithFurniture ,ModeOfPayment "
                + ",Deposit ,Negotiable ,DESCRIPTION ,realEstateImage ,realEstateStyleID ,realEstateStatus) "
                + "VALUES (? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,1)";
        try {
            System.out.println("111");
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, objRe.getRealEstateTitle());
            System.out.println("22222222");
            ps.setString(2, objRe.getCity());
            System.out.println("33333333");
            ps.setString(3, objRe.getStreet());
            System.out.println("4444444");
            ps.setString(4, objRe.getPrice());
            System.out.println("5555555");
            ps.setString(5, objRe.getNoOfBedrooms() + "");
            System.out.println("666666");
            ps.setString(6, objRe.getNoOfBathrooms() + "");
            System.out.println("7777777");
            ps.setInt(7, objRe.getRealEstateTypeID());
            System.out.println("888888");
            ps.setString(8, objRe.getArea());
            System.out.println("9999999");
            ps.setString(9, objRe.getOrientation());
            System.out.println("1010101");
            ps.setString(10, objRe.getExtraFacilitiesAvailable() + "");
            System.out.println("1111");
            ps.setString(11, objRe.getModesOfTransport());
            ps.setString(12, objRe.getWithFurniture() + "");
            ps.setString(13, objRe.getModeOfPayment());
            ps.setString(14, objRe.getDeposit());
            ps.setString(15, objRe.getNegotiable() + "");
            ps.setString(16, objRe.getDescription());
            ps.setString(17, objRe.getImage());
            System.out.println("12");
            ps.setString(18, objRe.getRealEstateStyleID() + "");
            System.out.println("13333");
            int i = ps.executeUpdate();
            System.out.println("14141414");
            if (i != 0) {
            rs = ps.getGeneratedKeys();
            rs.next();
            int realEstateID = rs.getInt(1);
            return realEstateID;
            }
        } catch (Exception e) {
            System.out.println("Loi o day");
            e.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, null);
        }
        return 0;
    }

    public RealEstate getRealEstateInfo(int realEstateID) {
        RealEstate realEstate = null;
        String realEstateTitle;
        String city;
        String street;
        String price;
        int noOfBedrooms;
        int noOfBathrooms;
        int realEstateTypeID;
        String area;
        String orientation;
        int extraFacilitiesAvailable;
        String modesOfTransport;
        int withFurniture;
        String modeOfPayment;
        String deposit;
        int negotiable;
        String description;
        int realEstateStyleID;
        String image;
        strCommand = "Select * from tbl_RealEstate WHERE RealEstateID = ?";
        try {
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setInt(1, realEstateID);
            rs = ps.executeQuery();
            if (rs.next()) {
                realEstateTitle = rs.getString("realEstateTitle");
                city = rs.getString("City");
                street = rs.getString("Street");
                price = rs.getString("Price");
                noOfBedrooms = rs.getInt("NoOfBedrooms");
                noOfBathrooms = rs.getInt("NoOfBathrooms");
                realEstateTypeID = rs.getInt("realEstateTypeID");
                area = rs.getString("area");
                orientation = rs.getString("orientation");
                extraFacilitiesAvailable = rs.getInt("ExtraFacilitiesAvailable");
                modesOfTransport = rs.getString("ModesOfTransport");
                withFurniture = rs.getInt("WithFurniture");
                modeOfPayment = rs.getString("ModeOfPayment");
                deposit = rs.getString("Deposit");
                negotiable = rs.getInt("Negotiable");
                description = rs.getString("Description");
                image = rs.getString("realEstateImage");
                System.out.println("Image: "+image);
                realEstateStyleID = rs.getInt("realEstateStyleID");
                realEstate = new RealEstate(realEstateID, realEstateTitle, city, street, price, noOfBedrooms, noOfBathrooms, realEstateTypeID, area, orientation, extraFacilitiesAvailable, modesOfTransport, withFurniture, modeOfPayment, deposit, negotiable, description, realEstateStyleID, image);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, rs);
        }
        return realEstate;
    }

    public ArrayList<realEstateInfo> getRealEstateByType(int realEstateTypeID) {
        ArrayList<realEstateInfo> listrealEstateInfo = new ArrayList<realEstateInfo>();
         System.out.println("Den day search roi");
        int realEstateID;
        String realEstateTitle;
        String description;
        String realEstateImage;
        String startDate;
        String endDate;
        int userID;
        int postHistoryID;
        realEstateInfo rei = null;
        postHistoryDAO objPostHis ;
        strCommand = "SELECT tbl_RealEstate.RealEstateID, tbl_RealEstate.realEstateTitle, tbl_RealEstate.Description,"
                + " tbl_RealEstate.realEstateImage, tbl_PostHistory.postHistoryID, tbl_PostHistory.startDate, "
                + "tbl_PostHistory.endDate, tbl_PostHistory.userID "
                + "FROM tbl_PostHistory INNER JOIN "
                + "tbl_RealEstate ON tbl_PostHistory.RealEstateID = tbl_RealEstate.RealEstateID WHERE tbl_PostHistory.postHistoryStatus = 1 AND tbl_RealEstate.realEstateStatus =1  AND tbl_RealEstate.realEstateTypeID = "+realEstateTypeID;
                System.out.println("Commad: "+strCommand);
        try {
            objPostHis = new postHistoryDAO();
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rs = ps.executeQuery();
            while (rs.next()) {
                boolean flag = false;
                rei = new realEstateInfo();
                realEstateID = rs.getInt("RealEstateID");
                realEstateTitle = rs.getString("realEstateTitle");
                description = rs.getString("Description");
                realEstateImage = rs.getString("realEstateImage");
                startDate = rs.getString("startDate");
                endDate = rs.getString("endDate");
                userID = rs.getInt("userID");
                postHistoryID = rs.getInt("postHistoryID");
                flag = checkDate(endDate);
                if (flag == true) {
                    rei.setRealEstateID(realEstateID);
                    rei.setRealEstateTitle(realEstateTitle);
                    rei.setDescription(description);
                    rei.setRealEstateImage(realEstateImage);
                    rei.setStartDate(startDate);
                    rei.setEndDate(endDate);
                    rei.setUserID(userID);
                    listrealEstateInfo.add(rei);
                } else {
                    objPostHis.closePost(postHistoryID);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, rs);
        }
        return listrealEstateInfo;
    }
    
    public ArrayList<realEstateInfo> getRealEstateByStyle(int realEstateStyleID) {
        ArrayList<realEstateInfo> listrealEstateInfo = new ArrayList<realEstateInfo>();
         System.out.println("Den day search roi");
        int realEstateID;
        String realEstateTitle;
        String description;
        String realEstateImage;
        String startDate;
        String endDate;
        int userID;
        int postHistoryID;
        realEstateInfo rei = null;
        postHistoryDAO objPostHis ;
        strCommand = "SELECT tbl_RealEstate.RealEstateID, tbl_RealEstate.realEstateTitle, tbl_RealEstate.Description,"
                + " tbl_RealEstate.realEstateImage, tbl_PostHistory.postHistoryID, tbl_PostHistory.startDate, "
                + "tbl_PostHistory.endDate, tbl_PostHistory.userID "
                + "FROM tbl_PostHistory INNER JOIN "
                + "tbl_RealEstate ON tbl_PostHistory.RealEstateID = tbl_RealEstate.RealEstateID WHERE tbl_PostHistory.postHistoryStatus = 1 AND tbl_RealEstate.realEstateStatus =1 AND tbl_RealEstate.realEstateStyleID = "+realEstateStyleID;
                System.out.println("Commad: "+strCommand);
        try {
            objPostHis = new postHistoryDAO();
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rs = ps.executeQuery();
            while (rs.next()) {
                boolean flag = false;
                rei = new realEstateInfo();
                realEstateID = rs.getInt("RealEstateID");
                realEstateTitle = rs.getString("realEstateTitle");
                description = rs.getString("Description");
                realEstateImage = rs.getString("realEstateImage");
                startDate = rs.getString("startDate");
                endDate = rs.getString("endDate");
                userID = rs.getInt("userID");
                postHistoryID = rs.getInt("postHistoryID");
                flag = checkDate(endDate);
                if (flag == true) {
                    rei.setRealEstateID(realEstateID);
                    rei.setRealEstateTitle(realEstateTitle);
                    rei.setDescription(description);
                    rei.setRealEstateImage(realEstateImage);
                    rei.setStartDate(startDate);
                    rei.setEndDate(endDate);
                    rei.setUserID(userID);
                    listrealEstateInfo.add(rei);
                } else {
                    objPostHis.closePost(postHistoryID);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, rs);
        }
        return listrealEstateInfo;
    }
}
